import FileSaver from 'file-saver';
import * as Excel from '@deepfos/exceljs';
import { getAllSheets, isCellData } from './api';
import Store from '../store';
import sheetmanage from '../controllers/sheetmanage';
import conditionformat from '../controllers/conditionformat';
import { rgbTohex, getDPI } from '../utils/util';
import { lineStyleLuckyToExceljs } from './stylesMap';
import { isNil, reverse } from 'lodash';
import { unescapexml, XLMLFormatMap } from './format';

const exportExcel = async function (value, param) {
    // luckysheetData.getluckysheetfile()获取的对象
    let luckysheetData = getAllSheets();
    // 1.创建工作簿，可以为工作簿添加属性
    const workbook = new Excel.Workbook();
    // 2.创建表格，第二个参数工作表的名称
    if (Object.prototype.toString.call(luckysheetData) === '[object Object]') {
        luckysheetData = [luckysheetData];
    }
    await transformLuckyJsonToExcel(luckysheetData, workbook, param);

    const buffer = workbook.xlsx.writeBuffer().then(data => {
        const blob = new Blob([data], {
            type: 'application/vnd.ms-excel;charset=utf-8'
        });
        console.log("导出成功！");
        FileSaver.saveAs(blob, `${value}.xlsx`);
    });
    return buffer;
};

const exportExcelFromJson = async function (sheetList, fileName, param) {
    const workbook = new Excel.Workbook();
    await transformLuckyJsonToExcel(sheetList, workbook, param);

    const buffer = workbook.xlsx.writeBuffer().then(data => {
        const blob = new Blob([data], {
            type: 'application/vnd.ms-excel;charset=utf-8'
        });
        console.log("导出成功！");
        FileSaver.saveAs(blob, `${fileName}.xlsx`);
    });
    return buffer;
};

const exportExcelBlobFromJson = async function (sheetList, fileName, param) {
    const workbook = new Excel.Workbook();
    await transformLuckyJsonToExcel(sheetList, workbook, param);
    const buffer = await workbook.xlsx.writeBuffer();
    const blob = new Blob([buffer], {
        type: 'application/vnd.ms-excel;charset=utf-8'
    });
    return blob;
};

const transformLuckyJsonToExcel = async function (luckysheetData, workbook, param = {}) {
    // isProtection 是否工作蒲保护
    const { isProtection = true } = param;
    for (let [tableIndex, table] of luckysheetData.entries()) {
        // const sheetName = Store.luckysheetfile[tableIndex].name || `sheet-${Store.luckysheetfile[tableIndex].index}`;

        const worksheet = workbook.addWorksheet(table.name);
        const merge = (table.config && table.config.merge) || {};
        const borderInfo = (table.config && table.config.borderInfo) || {};
        worksheet.state = table.hide === 1 ? 'hidden' : 'visible';
        worksheet.views.push({ state: 'normal' });
        if (table.color) {
            worksheet.properties.tabColor = { argb: transeformColor(table.color) };
        }

        // 设置冻结行列
        setFrozen(table.frozen, worksheet);
        // 设置筛选器
        setFilter(table.filter_select, worksheet);
        // 单位 万 亿 格式校验
        const wyreg = /^(w|y)(#,##)?((0?)|(0\.0+))/;
        // 遍历单元格数据
        table.data.forEach(function (row, rowid) {
            row.forEach(function (cell, columnid) {
                let target = worksheet.getCell(rowid + 1, columnid + 1);
                if (!cell) {
                    if (table.edit !== false) {
                        // 空白单元格不保护
                        target.protection = {
                            locked: false
                        };
                    }
                    return;
                }
                // 重置万亿单位 单元格 的格式
                if (cell.ct && cell.ct.fa && !!cell.ct.fa.match(wyreg)) {
                    // cell.ct.fa = cell.ct.fa.indexOf('w') === 0 ? '0\\.0,' : '0\\.00,,';
                    if (cell.ct.fa.indexOf('w') === 0) cell.ct.fa = '0\\.0,';
                    if (cell.ct.fa.indexOf('y') === 0 && !cell.ct.fa.toLocaleLowerCase().includes('yy')) cell.ct.fa = '0\\.00,,';
                }
                // 创建exceljs单元格

                // 设置填充
                fillConvert(cell.bg, target);

                // 默认单元格为保护态，对可编辑的单元格放开
                if (table.edit !== false && cell.edit !== false) {
                    target.protection = {
                        locked: false
                    };
                }
                // 设置批注
                if (cell.ps) {
                    target.note = cell.ps.value;
                }
                // 设置字体
                target.font = fontConvert(
                    cell.ff,
                    cell.fc,
                    cell.bl,
                    cell.it,
                    cell.fs,
                    cell.cl,
                    cell.un
                );
                if (cell && cell.ct && cell.ct.fa) {
                    if (cell.ct.fa !== 'General') {
                        let fmt = XLMLFormatMap[cell.ct.fa] || unescapexml(cell.ct.fa);
                        target.numFmt = fmt;
                    }
                }
                // 设置对齐
                let alignment = alignmentConvert(cell.vt, cell.ht, cell.tb, cell.tr, (cell.icon ? (cell.indent + 1) : cell.indent));
                target.alignment = alignment;

                // 设置值
                let value;
                if (cell.f) {
                    value = { formula: cell.f, result: cell.v };
                } else if (cell.ct && cell.ct.s) {
                    // xls转为xlsx之后，内部存在不同的格式，都会进到富文本里，即值不存在与cell.v，而是存在于cell.ct.s之后
                    // value = cell.ct.s[0].v
                    value = {
                        richText: cell.ct.s.map(item => {
                            return {
                                text: item.v,
                                font: fontConvert(
                                    item.ff,
                                    item.fc,
                                    item.bl,
                                    item.it,
                                    item.fs,
                                    item.cl,
                                    item.un
                                )
                            };
                        })
                    };
                } else if (typeof cell.editRender == 'function') {
                    value = cell.m;
                } else {
                    value = cell.v;
                }

                // 数字类型默认靠右
                if (cell.ct && cell.ct.t === 'n' && (cell.ht == 2 || isNil(cell.ht))) {
                    target.alignment.horizontal = 'right';
                }

                if (cell.ct && cell.ct.t === 'n' && cell.ct.fa && !cell.f && value !== '' && value !== null && !Number.isNaN(Number(value))) {
                    // FIXME 这块需要单独处理精度丢失问题
                    target.value = parseFloat(value);
                    // EG: value: 0.016 --> numFmt: '0.00%' --> 显示1.60%
                    // target.numFmt = cell.ct.fa;
                } else {
                    target.value = value;
                }
                // target.value = target.value || '';   //先注释掉，如果有默认空字符，会导致excel不能正确按溢出显示
            });
        });
        setMerge(merge, worksheet);
        // setBorder必须要在setMerge之后，因为合并单元格会更改每个单元格的border值，以左上角的单元格为准
        setBorder(borderInfo, worksheet);
        // 设置列宽,行高
        setColWidth(table.visibledatacolumn, worksheet);
        setRowHeight(table.visibledatarow, worksheet);
        // 设置列隐藏
        setRowColHidden(table.config?.colhidden, 'col', worksheet);
        // 设置行隐藏
        setRowColHidden(table.config?.rowhidden, 'row', worksheet);

        setConditionalFormatting(reverse(table.luckysheet_conditionformat_save), worksheet);

        setDataVerification(table.dataVerification, worksheet);

        if (isProtection) {
            await worksheet.protect('', {
                formatColumns: true,	// 允许用户格式化列
                formatRows: true,	// 允许用户格式化行
                insertRows: true,	// 允许用户插入行
                insertColumns: true,	// 允许用户插入列
                deleteRows: true,	// 允许用户删除行
                deleteColumns: true,	// 允许用户删除列
                sort: true,	// 允许用户对数据进行排序
                autoFilter: true	// 允许用户过滤表中的数据
            });
        } else {
            worksheet.unprotect();
        }
    }
};

const setMerge = function (luckyMerge = {}, worksheet) {
    const mergearr = Object.values(luckyMerge);
    mergearr.forEach(function (elem) {
        // elem格式：{r: 0, c: 0, rs: 1, cs: 2}
        // 按开始行，开始列，结束行，结束列合并（相当于 K10:M12）
        worksheet.mergeCells(
            elem.r + 1,
            elem.c + 1,
            elem.r + elem.rs,
            elem.c + elem.cs
        );
    });
};
const setBorder = function (luckyBorderInfo, worksheet) {
    if (!Array.isArray(luckyBorderInfo)) return;

    luckyBorderInfo.forEach(function (elem) {
        // 目前luckysheet通过工具栏只支持range类型
        if (elem.rangeType === 'range') {
            if (['border-outside', 'border-inside', 'border-horizontal', 'border-vertical', 'border-none'].includes(elem.borderType)) {
                let borderStyle = borderConvert(elem.style, elem.color);
                for (let rang of elem.range) {
                    let row = rang.row;
                    let column = rang.column;
                    if (!row.length || !column.length) return;
                    switch (elem.borderType) {
                    case 'border-outside':
                        // 第一行的上边框
                        for (let y = column[0] + 1; y <= column[1] + 1; y++) {
                            const cell = worksheet.getCell(row[0] + 1, y);
                            let border = {};
                            if (y === column[0] + 1) {
                                border.top = borderStyle;
                                border.left = borderStyle;
                            } else if (y === column[1] + 1) {
                                border.top = borderStyle;
                                border.right = borderStyle;
                            } else {
                                border.top = borderStyle;
                            }
                            cell.border = {
                                ...cell.border,
                                ...border
                            };
                        }
                        // 最后一行的下边框
                        for (let y = column[0] + 1; y <= column[1] + 1; y++) {
                            const cell = worksheet.getCell(row[1] + 1, y);
                            let border = {};
                            if (y === column[0] + 1) {
                                border.bottom = borderStyle;
                                border.left = borderStyle;
                            } else if (y === column[1] + 1) {
                                border.bottom  = borderStyle;
                                border.right = borderStyle;
                            } else {
                                border.bottom = borderStyle;
                            }
                            cell.border = {
                                ...cell.border,
                                ...border
                            };
                        }
                        // 第一列的左边框
                        for (let i = row[0] + 2; i <= row[1]; i++) {
                            const cell = worksheet.getCell(i, column[0] + 1);
                            cell.border = {
                                ...cell.border,
                                left: borderStyle
                            };
                        }
                        // 最后一列的右边框
                        for (let i = row[0] + 2; i <= row[1]; i++) {
                            const cell = worksheet.getCell(i, column[1] + 1);
                            cell.border = {
                                ...cell.border,
                                right: borderStyle
                            };
                        }
                        break;
                    case 'border-inside':
                        for (let i = row[0] + 1; i <= row[1] + 1; i++) {
                            for (let y = column[0] + 1; y <= column[1] + 1; y++) {
                                let border = {};

                                if (i !== row[1] + 1) {
                                    border.bottom = borderStyle;
                                }

                                if (y !== column[1] + 1) {
                                    border.right = borderStyle;
                                }

                                const cell = worksheet.getCell(i, y);
                                cell.border = {
                                    ...cell.border,
                                    ...border
                                }; ;
                            }
                        }
                        break;
                    case 'border-horizontal':
                        for (let i = row[0] + 1; i <= row[1] + 1; i++) {
                            for (let y = column[0] + 1; y <= column[1] + 1; y++) {
                                if (i !== row[1] + 1) {
                                    let border = {
                                        bottom: borderStyle
                                    };
                                    const cell = worksheet.getCell(i, y);
                                    cell.border = {
                                        ...cell.border,
                                        ...border
                                    };
                                }
                            }
                        }
                        break;
                    case 'border-vertical':
                        for (let i = row[0] + 1; i <= row[1] + 1; i++) {
                            for (let y = column[0] + 1; y <= column[1] + 1; y++) {
                                if (y !== column[1] + 1) {
                                    let border = {
                                        right: borderStyle
                                    };
                                    const cell = worksheet.getCell(i, y);
                                    cell.border = {
                                        ...cell.border,
                                        ...border
                                    };
                                }
                            }
                        }
                        break;
                    case 'border-none':
                        for (let i = row[0] + 1; i <= row[1] + 1; i++) {
                            for (let y = column[0] + 1; y <= column[1] + 1; y++) {
                                worksheet.getCell(i, y).border = null;
                            }
                        }
                        break;
                    default:
                        break;
                    }
                }
            } else {
                // border-all,border-top,border-right,border-bottom,border-left
                let borderStyle = borderConvert(elem.style, elem.color);
                for (let range of elem.range) {
                    const { row, column } = range;
                    let targetRow = [], targetColunm = [], borderPosition;
                    switch (elem.borderType) {
                    case 'border-all':
                        targetRow = row;
                        targetColunm = column;
                        borderPosition = ['top', 'right', 'bottom', 'left'];
                        break;
                    case 'border-top':
                        targetRow = [row[0]];
                        targetColunm = column;
                        borderPosition = ['top'];
                        break;
                    case 'border-right':
                        targetRow = row;
                        targetColunm = [column[1]];
                        borderPosition = ['right'];
                        break;
                    case 'border-bottom':
                        targetRow = [row[1]];
                        targetColunm = column;
                        borderPosition = ['bottom'];
                        break;
                    case 'border-left':
                        targetRow = row;
                        targetColunm = [column[0]];
                        borderPosition = ['left'];
                        break;
                    }

                    for (let x = targetRow[0]; x <= targetRow[targetRow.length - 1]; x++) {
                        for (let y = targetColunm[0]; y <= targetColunm[targetColunm.length - 1]; y++) {
                            const cell = worksheet.getCell(x + 1, y + 1);
                            cell.border = cell.border || {};
                            for (let p of borderPosition) {
                                cell.border[p] = borderStyle;
                            }
                        }
                    }
                }
            }
        }
        if (elem.rangeType === 'cell') {
            const borderInfo = elem.value;
            const { row_index, col_index } = borderInfo;
            const cell = worksheet.getCell(row_index + 1, col_index + 1);
            cell.border = cell.border || {};
            let count = 0;
            for (let p of ['l', 'r', 't', 'b']) {
                const data = borderInfo[p];
                if (data) {
                    count += 1;
                    const { style, color } = data;
                    const borderStyle = borderConvert(style, color);
                    let position;
                    switch (p) {
                    case 'l':
                        position = 'left'; break;
                    case 'r':
                        position = 'right'; break;
                    case 't':
                        position = 'top'; break;
                    case 'b':
                        position = 'bottom';
                    }
                    cell.border[position] = borderStyle;
                }
            }
            if (count === 0) cell.border = {};
        }
    });
};

const setRowHeight = function (rowlen, worksheet) {
    if (rowlen) {
        let dpi = getDPI();
        rowlen.forEach((accumulateHeight, index) => {
            let rowData = worksheet.getRow(index + 1);
            let rowHeight = index > 0 ? accumulateHeight - rowlen[index - 1] : accumulateHeight;
            rowData.height = Math.round(rowHeight * 72 / dpi);
        });
    }
};

const setColWidth = function (columnlen, worksheet) {
    if (columnlen) {
        columnlen.forEach((accumulateWidth, index) => {
            let colData = worksheet.getColumn(index + 1);
            // colData.isCustomWidth = true;
            let colWidth = index > 0 ? accumulateWidth - columnlen[index - 1] : accumulateWidth;
            let width;
            width = Math.round((colWidth + 0.83) / 8 - 5) + 5;
            /**
             * luckysheet
             * @columnWidth Excel column width
             * @return pixel column width
             */
            // function getColumnWidthPixel(columnWidth) {
            //   var pix = Math.round((columnWidth - 0.83) * 8 + 5);
            //   return pix;
            // }
            // TODO luckyexcel导入的时候会使用getColumnWidthPixel做单位转换,所以导出的时候也需要转一下
            colData.width = width;
        });
    }
};

/**
 * 设置行列隐藏
 * luckysheet rowhidden or colhidden
 * type row | col
 */
const setRowColHidden = function (hiddenObj = {}, type,  worksheet) {
    for (let index in hiddenObj) {
        if (hiddenObj[index] == 0) {
            let fn = type == 'row' ? 'getRow' : 'getColumn';
            worksheet[fn](parseInt(index) + 1).hidden = true;
        }
    }
};

/* ### frozen
- 类型：Array
- 默认值：[]
- 作用： 冻结行列设置，分为6种类型
    1. "row": 冻结首行
    2. "column": 冻结首列
    3. "both": 冻结行列
    4. "rangeRow": 冻结行到选区
    5. "rangeColumn": 冻结列到选区
    6. "rangeBoth": 冻结行列到选区
    7. "cancel": 取消冻结

    当设置冻结到选区的时候，需要设置开启冻结的单元格位置，格式为`{ row_focus:0, column_focus:0 }`，意为当前激活的单元格的行数和列数。

    sheet新的配置属性，存储更语义化的配置，用于初始化和传给后端。

    注意一点，luckysheetfile中还有一个配置freezen，其中的freezenhorizontaldata仍然用作本地数据，但是不发给后台存储，只做本地调试。

- 示例：
    - 冻结首行
    ```json
    {
        type: 'row'
    }
    ```
    - 冻结行到`'A1'`选区
     ```json
    {
        type: 'rangeRow',
        range: {row_focus: 0, column_focus: 0}
    }
    ```
    - 冻结行列到`'B2'`选区
     ```json
    {
        type: 'rangeBoth',
        range: {row_focus: 1, column_focus: 1}
    }
    ```
 *
 *
 * @param {*} frozen 冻结属性
 *            1. "row": 冻结首行
 *            2. "column": 冻结首列
 *            3. "both": 冻结行列
 *            4. "rangeRow": 冻结行到选区
 *            5. "rangeColumn": 冻结列到选区
 *            6. "rangeBoth": 冻结行列到选区
 *            7. "cancel": 取消冻结
 * @param {*} worksheet sheet数据结构
 */
const setFrozen = function (frozen, worksheet) {
    if (frozen && frozen.type && frozen.type !== 'cancel') {
        let frozenJson = { state: 'frozen', xSplit: 0, ySplit: 0 };
        let row;
        let col;
        if (frozen.range) {
            const { column_focus = -1, row_focus = -1 } = frozen.range;
            row = row_focus + 1;
            col = column_focus + 1;
        } else {
            row = 1;
            col = 1;
        }
        switch (frozen.type) {
        case 'row':  // 冻结首行
        case 'rangeRow':  // 冻结行到选区
            frozenJson.ySplit = row;
            break;
        case 'column':  // 冻结首列
        case 'rangeColumn':  // 冻结列到选区
            frozenJson.xSplit = col;
            break;
        case 'both':  // 冻结行列
        case 'rangeBoth':  // 冻结行列到选区
            frozenJson.ySplit = row;
            frozenJson.xSplit = col;
            break;
        }

        if (frozenJson.xSplit || frozenJson.ySplit) {
            worksheet.views.push(frozenJson);
        }
    }
};

/*
    params filter
    {
        row: [star,end],
        column: [star,end]
    }
    star,end起始行列从1开始
 */
const setFilter = function (filter, worksheet) {
    if (filter && filter.row && filter.column) {
        worksheet.autoFilter = {
            from: {
                row: filter.row[0] + 1 || 0,
                column: filter.column[0] + 1 || 0
            },
            to: {
                row: filter.row[1] + 1 || 0,
                column: filter.column[1] + 1 || 0
            }
        };
    }
};

/*
    设置条件格式
 */
function setConditionalFormatting (conditionalFormattingList, worksheet) {
    if (!conditionalFormattingList) return;

    for (let conditionalFormatting of conditionalFormattingList) {
        const {
            type: oType,
            conditionName,
            conditionValue,
            format,
            cellrange,
            conditionRange,
            conditionValOrigin = '',
            conditionValOrigin1
        } = conditionalFormatting;

        if (oType == 'default') {
            let type, operator, formulae, text, rank, percent, bottom, aboveAverage;
            switch (conditionName) {
            case 'greaterThan':
            case 'lessThan':
            case 'betweenness':
            case 'equal':
            case 'greaterThanOrEqual':
            case 'lessThanOrEqual':
            case 'notEqual':
            case 'notBetween':
                type = "cellIs";
                operator = conditionName == 'betweenness' ? 'between' : conditionName;
                formulae = conditionValue.map((num, index) => {
                    // betweenness下num为数组
                    num = num instanceof Array ? num[0] : num;
                    if (num === null) {
                        return index == 0 ? conditionValOrigin : conditionValOrigin1;
                    }
                    return Number(num);
                });
                break;
            case 'textContains':
                type = operator = "containsText";
                if (conditionRange && conditionRange.length && conditionRange[0]) {
                    text = conditionValOrigin.replace('=', '');
                } else {
                    text = `"${conditionValue[0]}"`;
                }
                break;
            case 'top10':
            case 'top10%':
            case 'last10':
            case 'last10%':
                type = "top10";
                rank = conditionValue[0];
                percent = /%/.test(conditionName);
                bottom = /last/.test(conditionName);
                break;
            case 'AboveAverage':
                type = "aboveAverage";
                aboveAverage = true;
                break;
            case 'SubAverage':
                type = "aboveAverage";
                aboveAverage = false;
                break;
            case 'duplicateValue':
                type = conditionValue[0] == 0 ? "duplicateValues" : "uniqueValues";
                break;
            case 'formula':
                type = "expression";
                formulae = conditionValue;
                break;
            default:
                continue;
            }

            worksheet.addConditionalFormatting({
                ref: conditionformat.getTxtByRange(cellrange),
                rules: [
                    {
                        type,
                        operator,
                        formulae,
                        text,
                        rank,
                        percent,
                        bottom,
                        aboveAverage,
                        style: {
                            font: { color: { argb: transeformColor(format.textColor || '') } },
                            fill: { type: 'pattern', pattern: 'solid', bgColor: { argb: transeformColor(format.cellColor || '') } }
                        }
                    }
                ]
            });
        } else if (oType == 'colorGradation') {  // 色阶
            worksheet.addConditionalFormatting({
                ref: conditionformat.getTxtByRange(cellrange),
                rules: [
                    {
                        type: 'colorScale',
                        cfvo: format.length == 2 ? [{ type: "min" }, { type: "max" }] : [{ type: "min" }, { type: "percentile", value: 50 }, { type: "max" }],
                        color: format.map((color) => {
                            return {
                                argb: transeformColor(color)
                            };
                        }).reverse()
                    }
                ]
            });
        } else if (oType == "icons") {  // 图集
            let iconSet, cfvo;
            const {
                selectDivision, conditionValue1, conditionValue2,
                condition1, condition2, conditionValue3, conditionValue4,
                condition3, condition4
            } = conditionalFormatting;

            const selectType = selectDivision == 'percentage' ? 'percent' : selectDivision;

            // '5Arrows' | '5ArrowsGray' | '5Boxes' | '5Quarters' | '5Rating' | '4Arrows' | '4ArrowsGray'
            // | '4Rating' | '4RedToBlack' | '4TrafficLights' | 'NoIcons' | '3Arrows' | '3ArrowsGray' | '3Flags' | '3Signs'
            // | '3Stars' | '3Symbols' | '3Symbols2' | '3TrafficLights1' | '3TrafficLights2' | '3Triangles';
            const typeMap = {
                'percentage': 'percent',
                'num': 'num'
            };
            const format2IconSet = {
                threeWayArrowMulticolor: '3Arrows',
                threeWayArrowGrayColor: '3ArrowsGray',
                threeTriangles: '3Triangles',
                fourWayArrowMulticolor: '4Arrows',
                fourWayArrowGrayColor: '4ArrowsGray',
                fiveWayArrowMulticolor: '5Arrows',
                fiveWayArrowGrayColor: '5ArrowsGray',
                threeColorTrafficLightRimless: '3TrafficLights1',
                threeColorTrafficLightBordered: '3TrafficLights2',
                threeSigns: '3Signs',
                fourColorTrafficLight: '4TrafficLights',
                greenRedBlackGradient: '4RedToBlack',
                threeSymbolsCircled: '3Symbols',
                threeSymbolsNoCircle: '3Symbols2',
                tricolorFlag: '3Flags',
                threeStars: '3Stars',
                fiveQuadrantDiagram: '5Quarters',
                fiveBoxes: '5Boxes',
                grade4: '4Rating',
                grade5: '5Rating'
            };
            iconSet = format2IconSet[format];

            switch (iconSet) {
            case '3Arrows':
            case '3ArrowsGray':
            case '3Flags':
            case '3Signs':
            case '3Stars':
            case '3Symbols':
            case '3Symbols2':
            case '3TrafficLights1':
            case '3TrafficLights2':
            case '3Triangles':
                if (!selectDivision) {
                    cfvo = [
                        { type: "percent", value: 0 },
                        { type: "percent", value: 33 },
                        { type: "percent", value: 66 }
                    ];
                } else {
                    cfvo = [
                        { type: "percent", value: 0 },
                        { type: selectType, value: conditionValue2, gte: Number(condition2 === "biggerthanandequal") },
                        { type: selectType, value: conditionValue1, gte: Number(condition1 === "biggerthanandequal") }
                    ];
                }
                break;
            case '4Arrows':
            case '4ArrowsGray':
            case '4Rating':
            case '4RedToBlack':
            case '4TrafficLights':
                if (!selectDivision) {
                    cfvo = [
                        { type: "percent", value: 0 },
                        { type: "percent", value: 25 },
                        { type: "percent", value: 50 },
                        { type: "percent", value: 75 }
                    ];
                } else {
                    cfvo = [
                        { type: "percent", value: 0 },
                        { type: selectType, value: conditionValue3, gte: Number(condition3 === "biggerthanandequal") },
                        { type: selectType, value: conditionValue2, gte: Number(condition2 === "biggerthanandequal") },
                        { type: selectType, value: conditionValue1, gte: Number(condition1 === "biggerthanandequal") }
                    ];
                }
                break;
            case '5Arrows':
            case '5ArrowsGray':
            case '5Boxes':
            case '5Quarters':
            case '5Rating':
                if (!selectDivision) {
                    cfvo = [
                        { type: "percent", value: 0 },
                        { type: "percent", value: 20 },
                        { type: "percent", value: 40 },
                        { type: "percent", value: 60 },
                        { type: "percent", value: 80 }
                    ];
                } else {
                    cfvo = [
                        { type: "percent", value: 0 },
                        { type: selectType, value: conditionValue4, gte: Number(condition4 === "biggerthanandequal") },
                        { type: selectType, value: conditionValue3, gte: Number(condition3 === "biggerthanandequal") },
                        { type: selectType, value: conditionValue2, gte: Number(condition2 === "biggerthanandequal") },
                        { type: selectType, value: conditionValue1, gte: Number(condition1 === "biggerthanandequal") }
                    ];
                }
            }

            worksheet.addConditionalFormatting({
                ref: conditionformat.getTxtByRange(cellrange),
                rules: [
                    {
                        type: 'iconSet',
                        iconSet,
                        cfvo
                    }
                ]
            });
        } else if (oType == 'dataBar') {
            worksheet.addConditionalFormatting({
                ref: conditionformat.getTxtByRange(cellrange),
                rules: [
                    {
                        type: 'dataBar',
                        color: { argb: transeformColor(format[0]) },
                        gradient: format.length == 2,
                        cfvo: [{ type: "min" }, { type: "max" }]
                    }
                ]
            });
        }
    }
}

/*
    设置数据验证
 */
function setDataVerification (dataVerification, worksheet) {
    if (!dataVerification) return;
    for (let key in dataVerification) {
        let [row, col] = key.split("_");
        row = parseInt(row);
        col = parseInt(col);
        const verification = dataVerification[key];
        const cell = worksheet.getCell(row + 1, col + 1);

        const { value1, value2 } = verification;
        let type, operator, formulae, defaultHintText;
        switch (verification.type) {
        case 'dropdown':
            type = 'list';
            formulae = [isCellData(value1) ? value1 : `"${value1}"`];
            defaultHintText = '请选择下拉列表中的选项';
            break;
        case 'number':
            type = 'decimal';
            defaultHintText = '请输入$的数值';
            break;
        case 'number_decimal':
            type = 'decimal';
            defaultHintText = '请输入$的小数';
            break;
        case 'number_integer':
            type = 'whole';
            defaultHintText = '请输入$的整数';
            break;
        case 'text_length':
            type = 'textLength';
            defaultHintText = '请输入$的文本';
            break;
        case 'date':
            type = 'date';
            defaultHintText = '请输入$的日期';
            break;
        case 'validateByFormula':
            type = 'custom';
            defaultHintText = '您输入的内容,不符合限制条件';
            formulae = [value1];
            break;
        default:
            // 不支持的不去处理，目前不支持的还有text_content,checkbox,validity
            continue;
        }

        switch (verification.type2) {
        case "bw":
            operator = 'between';
            formulae = [value1, value2];
            defaultHintText = defaultHintText.replace('$', `介于${value1}和${value2}之间`);
            break;
        case "nb":
            operator = 'notBetween';
            formulae = [value1, value2];
            defaultHintText = defaultHintText.replace('$', `不介于${value1}和${value2}之间`);
            break;
        case "eq":
            operator = 'equal';
            formulae = [value1];
            defaultHintText = defaultHintText.replace('$', `等于${value1}`);
            break;
        case "ne":
            operator = 'notEqual';
            formulae = [value1];
            defaultHintText = defaultHintText.replace('$', `不等于${value1}`);
            break;
        case "gt":
            operator = 'greaterThan';
            formulae = [value1];
            defaultHintText = defaultHintText.replace('$', `大于${value1}`);
            break;
        case "lt":
            operator = 'lessThan';
            formulae = [value1];
            defaultHintText = defaultHintText.replace('$', `小于${value1}`);
            break;
        case "gte":
            operator = 'greaterThanOrEqual';
            formulae = [value1];
            defaultHintText = defaultHintText.replace('$', `大于等于${value1}`);
            break;
        case "lte":
            operator = 'lessThanOrEqual';
            formulae = [value1];
            defaultHintText = defaultHintText.replace('$', `小于等于${value1}`);
            break;
        case "bf":
            operator = 'lessThan';
            formulae = [value1];
            defaultHintText = defaultHintText.replace('$', `早于${value1}`);
            break;
        case "nbf":
            operator = 'greaterThanOrEqual';
            formulae = [value1];
            defaultHintText = defaultHintText.replace('$', `不早于${value1}`);
            break;
        case "af":
            operator = 'greaterThan';
            formulae = [value1];
            defaultHintText = defaultHintText.replace('$', `晚于${value1}`);
            break;
        case "naf":
            operator = 'lessThanOrEqual';
            formulae = [value1];
            defaultHintText = defaultHintText.replace('$', `不晚于${value1}`);
            break;
        }

        cell.dataValidation = {
            type,
            operator,
            formulae,
            allowBlank: true,
            showInputMessage: verification.hintShow,
            showErrorMessage: verification.prohibitInput,
            prompt: verification.hintShow ? verification.hintText ? verification.hintText : defaultHintText : defaultHintText,
            error: defaultHintText
        };
    }
}

/**
 * 转化color为16进制
 * @param color
 * 可能本身就是16进制，也可能是rgb()形式
 */
function transeformColor (color) {
    if (color.toUpperCase().includes('RGB')) {
        color = rgbTohex(color);
    }
    color = color.replace(/^#/, '');
    color = color.toUpperCase();
    color = color.length == 3 ? color.replace(/(\w)(\w)(\w)/, '$1$1$2$2$3$3') : color;
    return color;
}

/*
    设置填充
    params bg 背景色
 */
const fillConvert = function (bg, cell) {
    if (bg) {
        cell.fill = {
            type: 'pattern',
            pattern: 'solid',
            fgColor: { argb: transeformColor(bg) }
        };
    }
};
const fontConvert = function (
    ff = 0,
    fc = '#000000',
    bl = 0,
    it = 0,
    fs = 10,
    cl = 0,
    un = 0
) {
    // luckysheet：ff(样式), fc(颜色), bl(粗体), it(斜体), fs(大小), cl(删除线), ul(下划线)
    const luckyToExcel = {
        0: '微软雅黑',
        1: 'PingFangSC-Regular',
        2: 'Times New Roman',
        3: 'Arial',
        4: 'Tahoma',
        5: 'Verdana',
        6: '宋体',
        7: '黑体',
        8: '楷书',
        9: '仿宋',
        10: '隶属',
        num2bl: function (num) {
            return num !== 0;
        }
    };

    let font = {
        name: typeof ff === 'number' ? luckyToExcel[ff] : typeof ff === 'string' ? ff : '微软雅黑',
        family: 1,
        size: fs,
        color: { argb: transeformColor(fc) },
        bold: luckyToExcel.num2bl(bl),
        italic: luckyToExcel.num2bl(it),
        underline: luckyToExcel.num2bl(un),
        strike: luckyToExcel.num2bl(cl)
    };
    return font;
};

const alignmentConvert = function (
    vt = 'default',
    ht = 'default',
    tb = 'default',
    tr = 'default',
    indent = 0
) {
    // luckysheet:vt(垂直), ht(水平), tb(换行), tr(旋转)
    const luckyToExcel = {
        vertical: {
            0: 'middle',
            1: 'top',
            2: 'bottom',
            default: 'middle'
        },
        horizontal: {
            0: 'center',
            1: 'left',
            2: 'right',
            default: 'left'
        },
        wrapText: {
            0: false,
            1: false,
            2: true,
            default: false
        },
        textRotation: {
            0: 0,
            1: 45,
            2: -45,
            3: 'vertical',
            4: 90,
            5: -90,
            default: 0
        }
    };
    let alignment = {
        vertical: luckyToExcel.vertical[vt],
        horizontal: luckyToExcel.horizontal[ht],
        wrapText: luckyToExcel.wrapText[tb],
        textRotation: luckyToExcel.textRotation[tr],
        indent: ht === 0 ? 0 : indent
    };
    return alignment;
};

/**
 * color可能是16进制也可能是rgb
 */
const borderConvert = function (style = 1, borderColor = '') {
    if (borderColor.indexOf("rgb") > -1) {
        borderColor = rgbTohex(borderColor);
    }
    return {
        style: lineStyleLuckyToExceljs[style],
        color: { argb: borderColor.replace('#', '') }
    };
};

export { exportExcel, exportExcelFromJson, exportExcelBlobFromJson };
